﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using UniversityManagementSystem.DAL;

namespace UniversityManagementSystem.Gateway
{
   [Serializable]
    public class TeacherGateway : GatewayConnection
   {
        private string commandString = null;
        [NonSerialized]
        private SqlDataReader getData = null;

        public List<string> GetAllDesignations()
        {

            List<string> allDesignations = new List<string>();
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("SELECT * FROM t_Designation");
                GetSqlCommand.CommandText = commandString;
                getData = GetSqlCommand.ExecuteReader();
                while (getData.Read())
                {
                    allDesignations.Add(getData["designation"].ToString());
                }
                GetSqlConn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return allDesignations;
        }

        public bool CheckEmail(string teacherEmail)
        {
            bool checkDone = false;
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("SELECT * FROM t_Teacher WHERE teacher_email = @teacherEmail");
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@teacherEmail", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@teacherEmail"].Value = teacherEmail;
                getData = GetSqlCommand.ExecuteReader();
                if (getData.Read())
                {
                    checkDone = true;
                }
                GetSqlConn.Close();
            }

            catch (Exception exception)
            {
                throw exception;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return checkDone;
        }

        public bool SaveTeacher(Teacher aTeacher)
        {
            bool saveTeacher = false;
            bool saveTeacherAcademic = false;
            bool saveDone = false;
            int saved = 0;
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("INSERT INTO t_Teacher(teacher_id,teacher_name,teacher_address,teacher_email,teacher_contact,teacher_designation,teacher_department)VALUES(@teacherId,@teacherName,@teacherAddress,@teacherEmail,@teacherContact,@teacherDesignation,@teacherDepartment)");
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@teacherId", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@teacherId"].Value = aTeacher.TeacherId;
                GetSqlCommand.Parameters.Add("@teacherName", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@teacherName"].Value = aTeacher.TeacherName;
                GetSqlCommand.Parameters.Add("@teacherAddress", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@teacherAddress"].Value = aTeacher.TeacherAddress;
                GetSqlCommand.Parameters.Add("@teacherEmail", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@teacherEmail"].Value = aTeacher.TeacherEmail;
                GetSqlCommand.Parameters.Add("@teacherContact", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@teacherContact"].Value = aTeacher.TeacherContact;
                GetSqlCommand.Parameters.Add("@teacherDesignation", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@teacherDesignation"].Value = aTeacher.TeacherDesignation;
                GetSqlCommand.Parameters.Add("@teacherDepartment", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@teacherDepartment"].Value = aTeacher.TeacherDeparment;
                saved = GetSqlCommand.ExecuteNonQuery();
                if (saved > 0)
                {
                    saveTeacher = true;
                }
                GetSqlConn.Close();
                saveTeacherAcademic = SaveTeacherAcademic(aTeacher);
                if(saveTeacher && saveTeacherAcademic)
                {
                    saveDone = true;
                }
            }
            catch (Exception exception)
            {
                throw new Exception("Error occured", exception);
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return saveDone;
        }

       private bool SaveTeacherAcademic(Teacher aTeacher)
       {
           bool saveTeacherAcademic = false;
           int saved = 0;
           try
           {
               GetSqlConn.Open();
               commandString = string.Format("INSERT INTO t_Teacher_academic(teacher_id,allocated_credit,remaining_credit,credit_overflow)VALUES(@teacherId,@teacherCredit,@teacherRemaining,@teacherOverflow)");
               GetSqlCommand.CommandText = commandString;
               GetSqlCommand.Parameters.Clear();
               GetSqlCommand.Parameters.Add("@teacherId", SqlDbType.VarChar);
               GetSqlCommand.Parameters["@teacherId"].Value = aTeacher.TeacherId;
               GetSqlCommand.Parameters.Add("@teacherCredit", SqlDbType.Float);
               GetSqlCommand.Parameters["@teacherCredit"].Value = aTeacher.TeacherCredit;
               GetSqlCommand.Parameters.Add("@teacherRemaining", SqlDbType.Float);
               GetSqlCommand.Parameters["@teacherRemaining"].Value = aTeacher.TeacherCredit;
               GetSqlCommand.Parameters.Add("@teacherOverflow", SqlDbType.Float);
               GetSqlCommand.Parameters["@teacherOverflow"].Value = 0;
               saved = GetSqlCommand.ExecuteNonQuery();
               if (saved > 0)
               {
                   saveTeacherAcademic = true;
               }
               GetSqlConn.Close();
           }
           catch (Exception exception)
           {
               throw new Exception("Error occured", exception);
           }
           finally
           {
               if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                   GetSqlConn.Close();
           }
           return saveTeacherAcademic;
       }


       public List<Teacher> GetTeachers(string department)
        {
            List<Teacher> getTeachers = new List<Teacher>();

            try
            {
                GetSqlConn.Open();
                commandString = string.Format("SELECT t_Teacher.teacher_name,t_Teacher_academic.teacher_id,t_Teacher_academic.allocated_credit,t_Teacher_academic.remaining_credit,t_Teacher_academic.credit_overflow FROM t_Teacher,t_Teacher_academic where t_Teacher_academic.teacher_id IN(SELECT teacher_id FROM t_Teacher WHERE teacher_department = @department) AND t_Teacher.teacher_department = @department");
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@department", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@department"].Value = department;
                getData = GetSqlCommand.ExecuteReader();
                while (getData.Read())
                {
                    Teacher teacher= new Teacher();
                    teacher.TeacherName = getData["teacher_name"].ToString();
                    teacher.TeacherId = getData["teacher_id"].ToString();
                    teacher.TeacherCredit = Convert.ToDouble(getData["allocated_credit"]);
                    teacher.TeacherRemainingCredit = Convert.ToDouble(getData["remaining_credit"]);
                    teacher.TeacherCreditOverflow = Convert.ToDouble(getData["credit_overflow"]);
                    getTeachers.Add(teacher);
                }
                GetSqlConn.Close();
            }
            catch (Exception exception)
            {
                throw exception;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return getTeachers;
        }

       public string GetTeacherNumber(string teacherDesignation)
       {
           string teacherNo = "";
           int number = 0;
           try
           {
               GetSqlConn.Open();
               commandString = string.Format("SELECT COUNT (*) AS number FROM t_Teacher WHERE teacher_designation = @teacherDesignation");
               GetSqlCommand.CommandText = commandString;
               GetSqlCommand.Parameters.Clear();
               GetSqlCommand.Parameters.Add("@teacherDesignation", SqlDbType.VarChar);
               GetSqlCommand.Parameters["@teacherDesignation"].Value = teacherDesignation;
               getData = GetSqlCommand.ExecuteReader();
               if(getData.Read())
               {
                   teacherNo = getData["number"].ToString();
               }
               GetSqlConn.Close();
           }
           catch (Exception exception)
           {
               throw exception;
           }
           finally
           {
               if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                   GetSqlConn.Close();
           }
           return teacherNo;
       }
   }
}